from typing import Optional

from sqlalchemy import and_
from sqlalchemy.orm import Session

from . import models, schemas


def form_user(user):
    if user is None:
        return
    # user.siblings = user.get_siblings()
    return user


def get_user(db: Session, user_id: int):
    user = db.query(models.User).filter(models.User.id == user_id).first()
    return user


def get_users(db: Session, skip: int = 0, limit: int = 100):
    users = db.query(models.User).offset(skip).limit(limit).all()
    return users


def get_users_by_id(db: Session, id_list):
    return db.query(models.User).filter(models.User.id.in_(id_list))


def mitigate_parent_child(db: Session, user_list: set[int], children_id: Optional[int] = None,
                          parent_id: Optional[int] = None):
    if not any((children_id, parent_id)):
        raise Exception("one param must be provided")

    for u_id in user_list:
        temp = models.ParentToChildrenTable(parent_id=parent_id or u_id, children_id=children_id or u_id)
        db.add(temp)


def create_user(db: Session, user: schemas.UserCreate):
    name = user.full_name.split()
    if len(name) < 2 or len(name) > 3:
        raise ValueError
    elif len(name) == 2:
        db_user = models.User(last_name=name[0], first_name=name[1])
    else:
        db_user = models.User(last_name=name[0], first_name=name[1], middle_name=name[2])

    db.add(db_user)

    db.commit()
    db.refresh(db_user)
    mitigate_parent_child(db, user.child_id_list, parent_id=db_user.id)
    mitigate_parent_child(db, user.parent_id_list, children_id=db_user.id)

    db.commit()
    db.refresh(db_user)
    return form_user(db_user)


def get_children(user):
    result = list()
    for child in user.children:
        data = {
            "id": child.id,
            "last_name": child.last_name,
            "first_name": child.first_name,
            "middle_name": child.middle_name
        }
        if child.children:
            data["children"] = get_children(child)
        result.append(data)
    return result


def get_user_tree(db: Session):
    result = list()
    user_with_no_parents = db.query(models.User).where(
        models.User.id.in_(
            db.query(models.ParentToChildrenTable.parent_id).where(
                models.ParentToChildrenTable.parent_id.not_in(
                    db.query(models.ParentToChildrenTable.children_id)
                )
            )
        )
    ).all()
    for u in user_with_no_parents:
        result.append(
            {
                "id": u.id,
                "last_name": u.last_name,
                "first_name": u.first_name,
                "middle_name": u.middle_name,
                "children": get_children(u)
            }
        )

    return result
